CREATE DATABASE picknroll_db;

USE picknroll_db;

CREATE TABLE Draft
(
	draft_id INT AUTO_INCREMENT NOT NULL,
	league VARCHAR(30) NOT NULL,
	league_link VARCHAR(200) NOT NULL,
	draft_link VARCHAR(200),
	draft_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(draft_id)
);

CREATE TABLE Player
(
	player_id INT AUTO_INCREMENT NOT NULL,
	rnk_yhoo INT,
	rnk_espn INT,
	rnk_razz INT,
	val_zsea DECIMAL(14, 12),
	val_zprj DECIMAL(4, 2),
	name VARCHAR(255),
	team VARCHAR(4),
	pos VARCHAR(10),
	gms INT,
	min DECIMAL(4,2),
	prj_fgm INT,
	prj_fga INT,
	prj_fg DECIMAL(4,3),
	prj_ftm INT,
	prj_fta INT,
	prj_ft DECIMAL(4,3),
	prj_3ptm INT,
	prj_pts INT,
	prj_reb INT,
	prj_ast INT,
	prj_stl INT,
	prj_blk INT,
	prj_to INT,
	sea_fgm INT,
	sea_fga INT,
	sea_fg DECIMAL(4,3),
	sea_ftm INT,
	sea_fta INT,
	sea_ft DECIMAL(4,3),
	sea_3ptm INT,
	sea_pts INT,
	sea_reb INT,
	sea_ast INT,
	sea_stl INT,
	sea_blk INT,
	sea_to INT,
	avg_3ptm DECIMAL(3, 2),
	avg_pts DECIMAL(4, 2),
	avg_reb DECIMAL(4, 2),
	avg_ast DECIMAL(4, 2),
	avg_stl DECIMAL(3, 2),
	avg_blk DECIMAL(3, 2),
	avg_to DECIMAL(4, 2),
	zsea_fg DECIMAL(14, 12),
	zsea_ft DECIMAL(14, 12),
	zsea_3ptm DECIMAL(14, 12),
	zsea_pts DECIMAL(14, 12),
	zsea_reb DECIMAL(14, 12),
	zsea_ast DECIMAL(14, 12),
	zsea_stl DECIMAL(14, 12),
	zsea_blk DECIMAL(14, 12),
	zsea_to DECIMAL(14, 12),
	zprj_fg DECIMAL(4, 2),
	zprj_ft DECIMAL(4, 2),
	zprj_3ptm DECIMAL(4, 2),
	zprj_pts DECIMAL(4, 2),
	zprj_reb DECIMAL(4, 2),
	zprj_ast DECIMAL(4, 2),
	zprj_stl DECIMAL(4, 2),
	zprj_blk DECIMAL(4, 2),
	zprj_to DECIMAL(4, 2),
	PRIMARY KEY(player_id)
);

CREATE TABLE Team
(
	team_id INT AUTO_INCREMENT NOT NULL,
	name VARCHAR(200) NOT NULL,
	prj_fgm INT,
	prj_fga INT,
	prj_fg DECIMAL(4,3),
	prj_ftm INT,
	prj_fta INT,
	prj_ft DECIMAL(4,3),
	prj_3ptm INT,
	prj_pts INT,
	prj_reb INT,
	prj_ast INT,
	prj_stl INT,
	prj_blk INT,
	prj_to INT,
	sea_fgm INT,
	sea_fga INT,
	sea_fg DECIMAL(4,3),
	sea_ftm INT,
	sea_fta INT,
	sea_ft DECIMAL(4,3),
	sea_3ptm INT,
	sea_pts INT,
	sea_reb INT,
	sea_ast INT,
	sea_stl INT,
	sea_blk INT,
	sea_to INT,
	zsea_fg DECIMAL(16, 12),
	zsea_ft DECIMAL(16, 12),
	zsea_3ptm DECIMAL(16, 12),
	zsea_pts DECIMAL(16, 12),
	zsea_reb DECIMAL(16, 12),
	zsea_ast DECIMAL(16, 12),
	zsea_stl DECIMAL(16, 12),
	zsea_blk DECIMAL(16, 12),
	zsea_to DECIMAL(16, 12),
	zprj_fg DECIMAL(6, 2),
	zprj_ft DECIMAL(6, 2),
	zprj_3ptm DECIMAL(6, 2),
	zprj_pts DECIMAL(6, 2),
	zprj_reb DECIMAL(6, 2),
	zprj_ast DECIMAL(6, 2),
	zprj_stl DECIMAL(6, 2),
	zprj_blk DECIMAL(6, 2),
	zprj_to DECIMAL(6, 2),
	last_update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(team_id)
);

CREATE TABLE Pick
(
	draft_id INT NOT NULL,
	player_id INT NOT NULL,
	team_id INT NOT NULL,
	round INT NOT NULL,
	pick_no INT NOT NULL,
	time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY (draft_id) REFERENCES Draft(draft_id),
	FOREIGN KEY (player_id) REFERENCES Player(player_id),
	FOREIGN KEY (team_id) REFERENCES Team(team_id),
	PRIMARY KEY(draft_id, player_id)
);